package DAOMysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;

import java.util.logging.Logger;

import model.BookDetailsDTO;
import DAO.BookDetailsDAO;
import DAO.NotFoundException;

public class BookDetailsDAOImpl implements BookDetailsDAO{
	
	private static final String dbUrl = "jdbc:mysql://localhost:3306/fbsdatabase";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";	
	
	public void insert(BookDetailsDTO bookDetails) throws NotFoundException{
		String insertSql= "Insert into bookingdetails value('"+bookDetails.getEmergency()+
				"','"+bookDetails.getPriority()+
				"','"+bookDetails.getFromDate()+
				"','"+bookDetails.getToDate()+
				"','"+bookDetails.getReason()+
				"','"+bookDetails.getFacilityName()+
				"','"+bookDetails.getBookedOn()+
				"','"+bookDetails.getBookedBy()+
				"','"+bookDetails.getStatus()+
				"','"+bookDetails.getModifiedBy()+
				"','"+bookDetails.getModifiedOn()+
				"','"+bookDetails.getComment()+"');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection conn = null;
		try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st = conn.createStatement();
			int res=st.executeUpdate(insertSql);
			st.close();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				conn.close();
			} 
			catch (Exception e){
			}
		}
	}
	
	public void insert(String emergency, String priority, String fromDate, String toDate,
						String reason, String facilityName, String bookedOn, int bookedBy,
						String status, int modifiedBy, String modifiedOn, String comment) throws NotFoundException{
		String insertSql= "Insert into bookingdetails (emergency,priority,fromDate,toDate,reason," +
				"facilityName,bookedOn,bookedBy,status,modifiedBy,modifiedOn,comment) values ('"+emergency+
				"','"+priority+"','"+fromDate+"','"+toDate+
				"','"+reason+"','"+facilityName+"','"+bookedOn+
				"','"+bookedBy+"','"+status+"','"+modifiedBy+"','"+modifiedOn+"','"+comment+"');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection conn = null;
		try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st = conn.createStatement();
			st.executeUpdate(insertSql);
			st.close();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				conn.close();
			} 
			catch (Exception e){
			}
		}
	}
	
	public void update(BookDetailsDTO bookDetails) throws NotFoundException{
    	String UpdateSql="Update bookingdetails SET Emergency='"+bookDetails.getEmergency()+
    			"',Priority='"+bookDetails.getPriority()+
    			"',FromDate='"+bookDetails.getFromDate()+
    			"',ToDate='"+bookDetails.getToDate()+
    			"',Reason='"+bookDetails.getReason()+
    			"',FacilityName='"+bookDetails.getFacilityName()+
				"',BookedOn='"+bookDetails.getBookedOn()+
				"',BookedBy='"+bookDetails.getBookedBy()+
				"',Status='"+bookDetails.getStatus()+
				"',ModifiedBy='"+bookDetails.getModifiedBy()+
				"',ModifiedOn='"+bookDetails.getModifiedOn()+
				"',Comment='"+bookDetails.getComment()+
				"'WHERE BookingID='"+bookDetails.getBookingID()+"';";
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	Connection conn=null;
    	try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st=conn.createStatement();
			int rows=st.executeUpdate(UpdateSql);
			st.close();
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
    }
	
	public void update(int bookingID, String emergency, String priority, String fromDate, String toDate,
			String reason, String facilityName, String bookedOn, int bookedBy,
			String status, int modifiedBy, String modifiedOn, String comment) throws NotFoundException{
		String UpdateSql="Update bookingdetails SET Emergency='"+emergency+
    			"',Priority='"+priority+"',FromDate='"+fromDate+"',ToDate='"+toDate+
    			"',Reason='"+reason+"',FacilityName='"+facilityName+"',BookedOn='"+bookedOn+
				"',BookedBy='"+bookedBy+"',Status='"+status+"',ModifiedBy='"+modifiedBy+
				"',ModifiedOn='"+modifiedOn+"',Comment='"+comment+
				"' WHERE BookingID='"+bookingID+"';";
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	Connection conn=null;
    	try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st=conn.createStatement();
			st.executeUpdate(UpdateSql);
			st.close();
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
    }

	public void cancel(BookDetailsDTO bookDetails) throws NotFoundException
    {
    	String cancelSql="Update bookingdetails SET Status='Cancel'WHERE BookingID='"+bookDetails.getBookingID()+"';";
    	
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	Connection conn=null;
    	try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st=conn.createStatement();
			int rows=st.executeUpdate(cancelSql);
			st.close();
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
    }
	
	public void delete(BookDetailsDTO bookDetails) throws NotFoundException
    {
    	String deleteSql="DELETE FROM bookingdetails where BookingID='"+bookDetails.getBookingID()+"';";
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	Connection conn=null;
    	try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st=conn.createStatement();
			st.executeUpdate(deleteSql);
			st.close();
		} catch (SQLException e) {
			String error="Error deleting records. Nested Exception is: "+e;
			System.out.println("#$###################");
			Logger.getLogger(getClass().getName(), null).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
    			conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	}    	
    }

	public BookDetailsDTO FindByBookingID(int bookingID) throws NotFoundException
    {
    	String FindByBookingIDSql="SELECT * FROM bookingdetails where BookingID='"+bookingID+"';";
    	try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    	Connection conn=null;
    	BookDetailsDTO bd = new BookDetailsDTO();
    	try {
			conn=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
			Statement st=conn.createStatement();
			ResultSet rs=st.executeQuery(FindByBookingIDSql);
			while(rs.next())
			{
				bd.setBookingID(rs.getInt("BookingID"));
				
				//populate pojo with all attr -daniel
				bd.setEmergency(rs.getString("Emergency"));
				bd.setPriority(rs.getString("Priority"));
				bd.setFromDate(rs.getDate("FromDate"));
				bd.setToDate(rs.getDate("ToDate"));
				bd.setReason(rs.getString("Reason"));
				bd.setFacilityName(rs.getString("FacilityName"));
				bd.setBookedOn(rs.getDate("BookedOn"));
				bd.setBookedBy(rs.getInt("BookedBy"));
				bd.setStatus(rs.getString("Status"));
				bd.setModifiedBy(rs.getInt("ModifiedBy"));
				bd.setModifiedOn(rs.getDate("ModifiedOn"));
				bd.setComment(rs.getString("Comment"));
				//end edit -daniel
			}
			st.close();
		} catch (SQLException e) {
			String error="Error deleting records. Nested Exception is: "+e;
			System.out.println("#$###################");
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}  		
    	} 
    	 return bd;
    
    }
	
	@Override
	public ArrayList<BookDetailsDTO> findAwaitingApproval()
			throws NotFoundException {
		ArrayList<BookDetailsDTO> items = new ArrayList<BookDetailsDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM bookingdetails where status in ('Book','Update') order by emergency desc , priority;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("bookingID"));
				bd.setEmergency(rs.getString("emergency"));
				bd.setPriority(rs.getString("priority"));
				bd.setFromDate(rs.getDate("fromDate"));
				bd.setToDate(rs.getDate("toDate"));
				bd.setReason(rs.getString("reason"));
				bd.setFacilityName(rs.getString("facilityName"));
				bd.setBookedOn(rs.getDate("bookedOn"));
				bd.setBookedBy(rs.getInt("bookedBy"));
				bd.setStatus(rs.getString("status"));
				bd.setModifiedBy(rs.getInt("modifiedBy"));
				bd.setModifiedOn(rs.getDate("modifiedOn"));
				bd.setComment(rs.getString("comment"));
				items.add(bd);
			}
			st.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
		  e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	
	public ArrayList<BookDetailsDTO> findAwaitingApprovalByUserID(int userID)
			throws NotFoundException {
		ArrayList<BookDetailsDTO> items = new ArrayList<BookDetailsDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM bookingdetails " +
				"where status in ('Book','Update') and BookedBy='"+userID+"'order by emergency desc , priority;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("bookingID"));
				bd.setEmergency(rs.getString("emergency"));
				bd.setPriority(rs.getString("priority"));
				bd.setFromDate(rs.getDate("fromDate"));
				bd.setToDate(rs.getDate("toDate"));
				bd.setReason(rs.getString("reason"));
				bd.setFacilityName(rs.getString("facilityName"));
				bd.setBookedOn(rs.getDate("bookedOn"));
				bd.setBookedBy(rs.getInt("bookedBy"));
				bd.setStatus(rs.getString("status"));
				bd.setModifiedBy(rs.getInt("modifiedBy"));
				bd.setModifiedOn(rs.getDate("modifiedOn"));
				bd.setComment(rs.getString("comment"));
				items.add(bd);
			}
			st.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
		  e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}
	
	public ArrayList<BookDetailsDTO> findBookingHistory() throws NotFoundException 
    {
		ArrayList<BookDetailsDTO> bookings=new ArrayList<BookDetailsDTO>();

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String selectSql="SELECT * FROM bookingdetails WHERE Status NOT IN('Book');";
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st=(Statement) conn.createStatement();
			ResultSet rs=(ResultSet) st.executeQuery(selectSql);
			
			while(rs.next())
			{
				BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("BookingID"));
				bd.setEmergency(rs.getString("Emergency"));
				bd.setPriority(rs.getString("Priority"));
				bd.setFromDate(rs.getDate("FromDate"));
				bd.setToDate(rs.getDate("ToDate"));
				bd.setReason(rs.getString("Reason"));
				bd.setFacilityName(rs.getString("FacilityName"));
				bd.setBookedOn(rs.getDate("BookedOn"));
				bd.setBookedBy(rs.getInt("BookedBy"));
				bd.setStatus(rs.getString("Status"));
				bd.setModifiedBy(rs.getInt("ModifiedBy"));
				bd.setModifiedOn(rs.getDate("ModifiedOn"));
				bd.setComment(rs.getString("Comment"));
				bookings.add(bd);
			}
			
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			Logger.getLogger(getClass().getName(), null).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
		return bookings;   	
    }
	
	public ArrayList<BookDetailsDTO> findAll() throws NotFoundException 
    {
		ArrayList<BookDetailsDTO> bookings=new ArrayList<BookDetailsDTO>();

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String selectSql="SELECT * FROM bookingdetails";
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st=(Statement) conn.createStatement();
			ResultSet rs=(ResultSet) st.executeQuery(selectSql);
			while(rs.next())
			{
				BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("BookingID"));
				bd.setEmergency(rs.getString("Emergency"));
				bd.setPriority(rs.getString("Priority"));
				bd.setFromDate(rs.getDate("FromDate"));
				bd.setToDate(rs.getDate("ToDate"));
				bd.setReason(rs.getString("Reason"));
				bd.setFacilityName(rs.getString("FacilityName"));
				bd.setBookedOn(rs.getDate("BookedOn"));
				bd.setBookedBy(rs.getInt("BookedBy"));
				bd.setStatus(rs.getString("Status"));
				bd.setModifiedBy(rs.getInt("ModifiedBy"));
				bd.setModifiedOn(rs.getDate("ModifiedOn"));
				bd.setComment(rs.getString("Comment"));
				bookings.add(bd);
			}
			
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			Logger.getLogger(getClass().getName(), null).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
		return bookings;   	
    }
	
	public  BookDetailsDTO findBookingByBookingID(int bookingID) throws NotFoundException{
		BookDetailsDTO bd=new BookDetailsDTO();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Try to connect");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String selectSql="SELECT * FROM bookingdetails WHERE BookingID="+bookingID+ ";";
		System.out.println(selectSql);

		Connection conn=null;
		try {
			conn=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			System.out.println(conn);
			Statement st=conn.createStatement();
			System.out.println("statement"+st);

			ResultSet rs=(ResultSet) st.executeQuery(selectSql);
			System.out.println("result"+rs);
			while(rs.next())
			{
				//BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("BookingID"));
				bd.setEmergency(rs.getString("Emergency"));
				bd.setPriority(rs.getString("Priority"));
				bd.setFromDate(rs.getDate("FromDate"));
				bd.setToDate(rs.getDate("ToDate"));
				bd.setReason(rs.getString("Reason"));
				bd.setFacilityName(rs.getString("FacilityName"));
				bd.setBookedOn(rs.getDate("BookedOn"));
				bd.setBookedBy(rs.getInt("BookedBy"));
				bd.setStatus(rs.getString("Status"));
				bd.setModifiedBy(rs.getInt("ModifiedBy"));
				bd.setModifiedOn(rs.getDate("ModifiedOn"));
				bd.setComment(rs.getString("Comment"));
				System.out.println("books1111111111111111111111111");
				System.out.println(bd);
			}
			st.close();
			//System.out.println(bookingDetail);
			System.out.println("books2222222222222222222222222");
			
		} 
		catch (SQLException e) {
			System.out.println("###############################################################");
			e.printStackTrace();
			System.out.println("###############################################################");
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			} catch (Exception e1) {
				
			}
    	} 
		return bd;   	
	}
	
	
	public ArrayList<BookDetailsDTO> findBookingHistoryById(int userID) throws NotFoundException 
    {
		ArrayList<BookDetailsDTO> bookings=new ArrayList<BookDetailsDTO>();

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String selectSql="SELECT * FROM bookingdetails WHERE Status NOT IN('Book') and BookedBy='"+userID+"'";
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st=(Statement) conn.createStatement();
			ResultSet rs=(ResultSet) st.executeQuery(selectSql);
			
			while(rs.next())
			{
				BookDetailsDTO bd=new BookDetailsDTO();
				bd.setBookingID(rs.getInt("BookingID"));
				bd.setEmergency(rs.getString("Emergency"));
				bd.setPriority(rs.getString("Priority"));
				bd.setFromDate(rs.getDate("FromDate"));
				bd.setToDate(rs.getDate("ToDate"));
				bd.setReason(rs.getString("Reason"));
				bd.setFacilityName(rs.getString("FacilityName"));
				bd.setBookedOn(rs.getDate("BookedOn"));
				bd.setBookedBy(rs.getInt("BookedBy"));
				bd.setStatus(rs.getString("Status"));
				bd.setModifiedBy(rs.getInt("ModifiedBy"));
				bd.setModifiedOn(rs.getDate("ModifiedOn"));
				bd.setComment(rs.getString("Comment"));
				bookings.add(bd);
			}
			
		} catch (SQLException e) {
			String error="Error updating records. Nested Exception is: "+e;
			System.out.println("###############################################################");
			Logger.getLogger(getClass().getName(), null).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		}
    	finally
    	{
    		try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}   		
    	} 
		return bookings;   	
    }
	
	
//	public ArrayList<BookDetailsDTO> findBookingByUserId() throws NotFoundException 
//    {
//		ArrayList<BookDetailsDTO> bookings=new ArrayList<BookDetailsDTO>();
//
//		try {
//			Class.forName("com.mysql.jdbc.Driver");
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
//		String selectSql="SELECT * FROM bookingdetails WHERE Status NOT IN('Book');";
//		Connection conn=null;
//		try {
//			conn=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
//			Statement st=(Statement) conn.createStatement();
//			ResultSet rs=(ResultSet) st.executeQuery(selectSql);
//			
//			while(rs.next())
//			{
//				BookDetailsDTO bd=new BookDetailsDTO();
//				bd.setBookingID(rs.getInt("BookingID"));
//				bd.setEmergency(rs.getString("Emergency"));
//				bd.setPriority(rs.getString("Priority"));
//				bd.setFromDate(rs.getDate("FromDate"));
//				bd.setToDate(rs.getDate("ToDate"));
//				bd.setReason(rs.getString("Reason"));
//				bd.setFacilityName(rs.getString("FacilityName"));
//				bd.setBookedOn(rs.getDate("BookedOn"));
//				bd.setBookedBy(rs.getInt("BookedBy"));
//				bd.setStatus(rs.getString("Status"));
//				bd.setModifiedBy(rs.getInt("ModifiedBy"));
//				bd.setModifiedOn(rs.getDate("ModifiedOn"));
//				bd.setComment(rs.getString("Comment"));
//				bookings.add(bd);
//			}
//			
//		} catch (SQLException e) {
//			String error="Error updating records. Nested Exception is: "+e;
//			System.out.println("###############################################################");
//			Logger.getLogger(getClass().getName(), null).log(Level.SEVERE, error);
//			throw new NotFoundException(error);
//		}
//    	finally
//    	{
//    		try {
//				conn.close();
//			} catch (SQLException e) {				
//				e.printStackTrace();
//			}   		
//    	} 
//		return bookings;   	
//    }

}